package cz.muni.fi.pv168.reminder.managers.implementation;

import cz.muni.fi.pv168.reminder.DBUtils;
import cz.muni.fi.pv168.reminder.ServiceFailureException;
import cz.muni.fi.pv168.reminder.managers.WorkerManager;
import cz.muni.fi.pv168.reminder.entities.Worker;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author Katarina Dusickova, 395973
 */
public class WorkerManagerImpl implements WorkerManager {

    private DataSource ds;
    public static final Logger logger = Logger.getLogger(WorkerManagerImpl.class.getName());

    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

    private void checkDataSource() {
        if (ds == null) {
            throw new IllegalStateException("DataSource is not set");
        }
    }

    @Override
    public void createPerson(Worker person) throws ServiceFailureException {

        checkDataSource();

        if (person == null) {
            throw new IllegalArgumentException("Person is null");
        }
        if (person.getId() != null) {
            throw new IllegalArgumentException("Person id is already set");
        }

        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            conn.setAutoCommit(false);

            st = conn.prepareStatement("INSERT INTO WORKER (name,email) VALUES (?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            st.setString(1, person.getName());
            st.setString(2, person.getEmailAddress());

            int count = st.executeUpdate();
            DBUtils.checkUpdatesCount(count, person, true);
            Long id = DBUtils.getId(st.getGeneratedKeys());

            person.setId(id);
            conn.commit();
            logger.log(Level.SEVERE, "Task added", person);
        } catch (SQLException ex) {
            String msg = "Error when inserting person into db";
            logger.log(Level.SEVERE, null, ex);
            throw new ServiceFailureException(msg, ex);
        } finally {
            DBUtils.doRollbackQuietly(conn);
            DBUtils.closeQuietly(conn, st);
        }

    }

    @Override
    public void updatePerson(Worker person) throws ServiceFailureException {
        checkDataSource();
        Connection conn = null;
        PreparedStatement st = null;
        if (person == null) {
            throw new IllegalArgumentException("person is null");
        }
        if (person.getId() == null) {
            throw new IllegalArgumentException("id is null");
        }
        if (person.getName() == null) {
            throw new IllegalArgumentException("person name is null");
        }
        if (person.getEmailAddress() == null) {
            throw new IllegalArgumentException("person email is null");
        }

        try {

            conn = ds.getConnection();
            conn.setAutoCommit(false);
            st = conn.prepareStatement("UPDATE Worker SET name = ?, email = ? WHERE id = ?");
            st.setString(1, person.getName());
            st.setString(2, person.getEmailAddress());
            st.setLong(3, person.getId());

            int count = st.executeUpdate();
            if (count != 1) {
                throw new ServiceFailureException("Internal Error: More rows "
                        + "inserted when trying to insert person " + person);
            }

            DBUtils.checkUpdatesCount(count, person, true);
            conn.commit();
            logger.log(Level.SEVERE, "Person updateded", person);
        } catch (SQLException ex) {
            String msg = "Error when inserting person into db";
            logger.log(Level.SEVERE, null, ex);
            throw new ServiceFailureException(msg, ex);
        } finally {
            DBUtils.doRollbackQuietly(conn);
            DBUtils.closeQuietly(conn, st);
        }
    }

    @Override
    public void deletePerson(Worker person) throws ServiceFailureException {

        checkDataSource();
        Connection conn = null;
        PreparedStatement st = null;

        if (person == null) {
            throw new IllegalArgumentException("Person is null");
        }
        if (person.getId() == null) {
            throw new IllegalArgumentException("Person id is null");
        }

        try {
            conn = ds.getConnection();
            conn.setAutoCommit(false);
            st = conn.prepareStatement("DELETE FROM Worker WHERE id = ?");
            st.setLong(1, person.getId());

            int count = st.executeUpdate();
            DBUtils.checkUpdatesCount(count, person, false);
            conn.commit();
            logger.log(Level.SEVERE, "Person deleted", person);
        } catch (SQLException ex) {
            String msg = "Error when deleting person from db";
            logger.log(Level.SEVERE, null, ex);
            throw new ServiceFailureException(msg, ex);
        } finally {
            DBUtils.doRollbackQuietly(conn);
            DBUtils.closeQuietly(conn, st);
        }

    }

    @Override
    public Worker findPersonById(Long id) throws ServiceFailureException {
        checkDataSource();
        if (id == null) {
            throw new IllegalArgumentException("id is null");
        }
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT id, Name, email FROM Worker WHERE id = ?");
            st.setLong(1, id);
            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                Worker person = resultSetToWorker(rs);

                if (rs.next()) {
                    throw new ServiceFailureException(
                            "Internal error: More entities with the same id found "
                            + "(source id: " + id + ", found " + person + " and " + resultSetToWorker(rs));
                }

                return person;
            } else {
                return null;
            }

        } catch (SQLException ex) {
            throw new ServiceFailureException(
                    "Error when retrieving person with id " + id, ex);
        } finally {
            DBUtils.closeQuietly(conn, st);

        }
    }

    @Override
    public List<Worker> findAllPersons() throws ServiceFailureException {
        checkDataSource();
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = ds.getConnection();
            st = conn.prepareStatement(
                    "SELECT id,name,email FROM worker");
            ResultSet rs = st.executeQuery();

            List<Worker> result = new ArrayList<>();
            while (rs.next()) {
                result.add(resultSetToWorker(rs));
            }
            return result;

        } catch (SQLException ex) {
            throw new ServiceFailureException("Error when retrieving all persons", ex);
        } finally {
            DBUtils.closeQuietly(conn, st);
        }
    }



    private Long getKey(ResultSet keyRS, Worker person) throws ServiceFailureException, SQLException {
        if (keyRS.next()) {
            if (keyRS.getMetaData().getColumnCount() != 1) {
                throw new ServiceFailureException("Internal Error: Generated key"
                        + "retriving failed when trying to insert person " + person
                        + " - wrong key fields count: " + keyRS.getMetaData().getColumnCount());
            }
            Long result = keyRS.getLong(1);
            if (keyRS.next()) {
                throw new ServiceFailureException("Internal Error: Generated key"
                        + "retriving failed when trying to insert person " + person
                        + " - more keys found");
            }
            return result;
        } else {
            throw new ServiceFailureException("Internal Error: Generated key"
                    + "retriving failed when trying to insert grave " + person
                    + " - no key found");
        }
    }

    private Worker resultSetToWorker(ResultSet rs) throws SQLException {
        Worker person = new Worker();
        person.setId(rs.getLong("id"));
        person.setName(rs.getString("name"));
        person.setEmailAddress(rs.getString("email"));
        return person;
    }
}
